﻿using System;
using System.Collections.Generic;
using System.Linq;
using KPIS.DBM;
using System.Data;
using KPIS.GERP.GFMS.IDAL;
using KPIS.GERP.GFMS.MODEL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_Payroll : IDAL_FIM_Payroll
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        D_BGM_ProjectOperationExpense projectOperationExpenseDal = new D_BGM_ProjectOperationExpense();
        D_BGM_ProjectOperation projectOperationDal = new D_BGM_ProjectOperation();
        D_BGM_Project projectDal = new D_BGM_Project();
        D_BGM_ProjectExpense projectExpenseDal = new D_BGM_ProjectExpense();

        #region Implementation of IDAL_FIM_Payroll
        
        public IList<M_FIM_Payroll> GetPayrolls(int? payroll_seq = null, string payroll_ref_no = null)
        {
            string strSQL = @"SELECT [payroll_seq]       ,[ref_no], [emp_type_seq]
                                  ,[req_date]      ,[bgm_exp_grp_seq]      ,[year_no]
                                  ,[bgm_year_no]      ,[month_no]      ,[title]      ,[ConId]      
                                  ,[amount]      ,[bgm_proj_oper_exp_seq]      ,[record_status]
                                  ,[created_by]     ,[created_when]     ,[updated_by]       ,[updated_when]
                              FROM [dbo].[fim_payroll]
                              WHERE record_status != @delete_status" 
                            + (payroll_seq.HasValue ? " AND payroll_seq = @payroll_seq":"")
                            + (!string.IsNullOrEmpty(payroll_ref_no) ? " AND ref_no like @payroll_ref_no":""); 

            DataTable dt = ExecuteDataTable(strSQL, new Dictionary<string, object>{{"@payroll_seq", payroll_seq}, {"@payroll_ref_no", payroll_ref_no}});
            var masContacts = GetIcmMasContact();
            var projOperExps = projectOperationExpenseDal.GetFull(null, null, null);
            var projOper = projectOperationDal.GetFull(null, null, null, null, null, true, false);
            var proj = projectDal.GetFull(null, null, null);
            var projExp = projectExpenseDal.GetFull(null, null, null);
            var payrollDeductions = GetPayrollDeductions(null, null);
            var datas = new List<M_FIM_Payroll>();
            foreach (DataRow dr in dt.Rows)
            {
                var payroll = new M_FIM_Payroll();
                payroll.payroll_seq =Convert.ToInt32(dr["payroll_seq"]); 
                payroll.ref_no = Convert.ToString(dr["ref_no"]);
                payroll.emp_type_seq = Convert.ToInt32(dr["emp_type_seq"]);
                payroll.req_date = Convert.ToDateTime(dr["req_date"]);
                payroll.bgm_exp_grp_seq = Convert.ToInt32(dr["bgm_exp_grp_seq"]);
                payroll.year_no = Convert.ToInt32(dr["year_no"]);
                payroll.bgm_year_no = Convert.ToInt32(dr["bgm_year_no"]);
                payroll.month_no = Convert.ToInt32(dr["month_no"]);
                payroll.title = Convert.ToString(dr["title"]);
                payroll.ConId = cn.ConvertNullInt(dr["ConId"]);
                payroll.amount = Convert.ToDecimal(dr["amount"]);;
                payroll.bgm_proj_oper_exp_seq = cn.ConvertNullInt(dr["bgm_proj_oper_exp_seq"]);
                payroll.record_status = Convert.ToString(dr["record_status"]);
                payroll.created_by = Convert.ToInt32(dr["created_by"]);
                payroll.created_when = Convert.ToDateTime(dr["created_when"]);
                payroll.updated_by = cn.ConvertNullInt(dr["updated_by"]);
                payroll.updated_when = cn.ConvertNullDateTime(dr["updated_when"]);
                payroll.IcmMasContact = masContacts.FirstOrDefault(o => o.ConId == payroll.ConId);
                payroll.BgmProjectOperationExpense = projOperExps.FirstOrDefault(o => o.bgm_proj_oper_exp_seq == payroll.bgm_proj_oper_exp_seq);
                if(payroll.BgmProjectOperationExpense != null)
                {
                    payroll.BgmProjectOperationExpense.m_bgm_projectexpense =
                        projExp.FirstOrDefault(o => o.bgm_proj_exp_seq == payroll.BgmProjectOperationExpense.bgm_proj_exp_seq);
                    if(payroll.BgmProjectOperationExpense.m_bgm_projectexpense != null)
                    {
                        payroll.BgmProjectOperationExpense.m_bgm_project = projOper.FirstOrDefault(o => o.bgm_proj_seq == payroll.BgmProjectOperationExpense.m_bgm_projectexpense.bgm_proj_seq);
                    }
                }

                payroll.PayrollDeductions = payrollDeductions.Where(o => o.payroll_seq == payroll.payroll_seq).ToList();
                 
                datas.Add(payroll);
            }
             

            return datas;
        }

        public IList<M_FIM_Payroll> GetPayrolls(int month, int year)
        {
            return GetPayrolls().Where(o => o.month_no == month && o.year_no == year).ToArray();
        }

        public IList<M_ICM_MasContact> GetIcmMasContact()
        { 
            string strSQL = @"SELECT [ConId]      ,[NameTh]      ,[NameEng]      ,[ConCategory]
                                  ,[ConGrpId]      ,[TaxCode]      ,[RegistedDate]      ,[RegistedCapital]
                                  ,[GfmisCode]      ,[Coordinator]      ,[VatTypeId]      ,[ConStatusId]
                                  ,[StatRemarks]      ,[Country]      ,[Building]      ,[Floor]
                                  ,[Village]      ,[Address]      ,[Road]      ,[Lane]
                                  ,[District]      ,[Amphur]      ,[Postal]      ,[Province]
                                  ,[Tel]      ,[TelNext]      ,[Fax]      ,[FaxNext]
                                  ,[Email]      ,[RecordStatus]      ,[CreatedBy]      ,[CreatedWhen]
                                  ,[UpdatedBy]      ,[UpdatedWhen]
                              FROM [dbo].[IcmMasContact] 
                              WHERE RecordStatus != @delete_status "; 
            DataTable dt = ExecuteDataTable(strSQL, null);
            var datas = new List<M_ICM_MasContact>();
            foreach (DataRow dr in dt.Rows)
            {
                var masContact = new M_ICM_MasContact();
                masContact.ConId = Convert.ToInt32(dr["ConId"]);
                masContact.NameTh = Convert.ToString(dr["NameTh"]);
                masContact.ConCategory = Convert.ToInt32(dr["ConCategory"]);
                masContact.ConGrpId = Convert.ToInt32(dr["ConGrpId"]);
                datas.Add(masContact);
            }
            
            return datas;
        }

        public bool CheckSentToPayment(int month, int year)
        {
            string strSQL = @"  SELECT * 
                                  FROM fim_payroll_history 
                                  WHERE year_no = @year_no 
                                    AND month_no = @month_no 
                                    AND record_status != @delete_status";
            var parameters = new Dictionary<string, object>
                {
                    {"@year_no", year},
                    {"@month_no", month}
                };
            return ExecuteDataTable(strSQL, parameters).Rows.Count > 0;
        }

        public int GetCurrentBgmYear()
        {
            string strSQL = @"SELECT bgm_sys_control_values
                                FROM bgm_system_control 
                                WHERE bgm_sys_control_name = 'bg_year'";
            return Convert.ToInt32(ExecuteDataTable(strSQL, null).Rows[0]["bgm_sys_control_values"]);
        }

        public int InsertPayroll(M_FIM_Payroll model)
        {
            string strSQL = @" INSERT INTO [dbo].[fim_payroll]
                                   ( [emp_type_seq]           ,[ref_no],       [req_date]
                                   ,[bgm_exp_grp_seq]           ,[year_no]           ,[bgm_year_no]
                                   ,[month_no]           ,[title]           ,[ConId]
                                   ,[amount]           ,[bgm_proj_oper_exp_seq]           ,[record_status]
                                   ,[created_by]          ,[created_when])
                             VALUES (
                                     @emp_type_seq           ,             @ref_no,           @req_date
                                   ,@bgm_exp_grp_seq           ,@year_no           ,@bgm_year_no
                                   ,@month_no           ,@title           ,@ConId
                                   ,@amount           ,@bgm_proj_oper_exp_seq           ,@record_status
                                   ,@created_by         ,GETDATE());

                            SELECT SCOPE_IDENTITY()";

            var parameters = new Dictionary<string, object>
                { 
                    {"@ref_no", model.ref_no},
                    {"@emp_type_seq", model.emp_type_seq},
                    {"@req_date", model.req_date},
                    {"@bgm_exp_grp_seq", model.bgm_exp_grp_seq},
                    {"@year_no", model.year_no},
                    {"@bgm_year_no", model.bgm_year_no},
                    {"@month_no", model.month_no},
                    {"@title", model.title},
                    {"@ConId", model.ConId},
                    {"@amount", model.amount},
                    {"@bgm_proj_oper_exp_seq", model.bgm_proj_oper_exp_seq},
                    {"@record_status", model.record_status},
                    {"@created_by", model.created_by},
                };
            DataTable dtLastId = ExecuteDataTable(strSQL, parameters);
            return Convert.ToInt32(dtLastId.Rows[0][0]);
        }

        public void UpdatePayroll(M_FIM_Payroll model)
        {
            if(model == null || GetPayrolls(model.payroll_seq).Count == 0)
            {
                return;
            }

            string strSQL = @"UPDATE [dbo].[fim_payroll]
                               SET [ref_no] = @ref_no
                                  ,[emp_type_seq] = @emp_type_seq
                                  ,[req_date] = @req_date
                                  ,[bgm_exp_grp_seq] = @bgm_exp_grp_seq

                                  ,[year_no] = @year_no
                                  ,[bgm_year_no] = @bgm_year_no
                                  ,[month_no] = @month_no
                                  ,[title] = @title

                                  ,[ConId] = @ConId
                                  ,[amount] = @amount
                                  ,[bgm_proj_oper_exp_seq] = @bgm_proj_oper_exp_seq
                                  ,[record_status] = @record_status

                                  ,[updated_by] = @updated_by
                                  ,[updated_when] = GETDATE()
                             WHERE payroll_seq = @payroll_seq";
             
            var parameters = new Dictionary<string, object>
                {
                    {"@payroll_seq", model.payroll_seq},
                    {"@ref_no", model.ref_no}, 
                    {"@emp_type_seq", model.emp_type_seq},
                    {"@req_date", model.req_date},
                    {"@bgm_exp_grp_seq", model.bgm_exp_grp_seq},
                    {"@year_no", model.year_no},
                    {"@bgm_year_no", model.bgm_year_no},
                    {"@month_no", model.month_no},
                    {"@title", model.title},
                    {"@ConId", model.ConId},
                    {"@amount", model.amount},
                    {"@bgm_proj_oper_exp_seq", model.bgm_proj_oper_exp_seq},
                    {"@record_status", model.record_status},
                    {"@updated_by", model.updated_by}
                };
            ExecuteDataTable(strSQL, parameters); 
        }

        public void DeletePayroll(int payroll_seq, int emp_seq)
        {
            string strSQL = @"UPDATE [dbo].[fim_payroll] 
                                SET [record_status] = @delete_status
                                    ,[updated_by] = @emp_seq
                                    ,[updated_when] = GETDATE()
                                WHERE payroll_seq = @payroll_seq ";
             
            var parameters = new Dictionary<string, object>
                {
                    {"@payroll_seq", payroll_seq},
                    {"@emp_seq", emp_seq}
                };
            ExecuteDataTable(strSQL, parameters);

            foreach(var payrollDeduction in GetPayrollDeductions(payroll_seq))
            {
                DeletePayrollDeduction(payrollDeduction.payroll_deduction_seq, emp_seq);
            }
        }

        public void MarkAsSentToPayment(int month, int year, int emp_seq)
        {
            bool isFoundSentToPayment = false;
            var parameters = new Dictionary<string, object>
                {
                    {"@year_no", year},
                    {"@month_no", month},
                    {"@emp_seq", emp_seq}
                };
            DataTable dt = ExecuteDataTable("SELECT record_status from  fim_payroll_history where month_no = @month_no and year_no = @year_no", parameters);
            isFoundSentToPayment = dt.Rows.Count != 0;
            if(isFoundSentToPayment)
            {
                string strSQL = @"UPDATE [fim_payroll_history] 
                                    SET [record_status] = @used_status
                                        ,[updated_by] = @emp_seq
                                        ,[updated_when] = GETDATE()
                                    WHERE  [month_no] = @month_no AND [year_no] = @year_no";
                ExecuteDataTable(strSQL, parameters);
            }
            else
            {
                string strSQL = @"INSERT INTO [dbo].[fim_payroll_history] (
                                    [month_no] ,[year_no] ,[record_status] ,[created_by] ,[created_when])
                                VALUES (
                                    @month_no, @year_no, @used_status, @emp_seq, GETDATE()) ";
                ExecuteDataTable(strSQL, parameters);
            }
        }

        public void MarkAsNotSentToPayment(int month, int year, int emp_seq)
        {
            if(!CheckSentToPayment(month, year))
            {
                return;
            }

            string strSQL = @"UPDATE [dbo].[fim_payroll_history]
                               SET [record_status] = @delete_status
                                  ,[updated_by] = @updated_by
                                  ,[updated_when] = GETDATE()
                             WHERE  [month_no] = @month_no AND [year_no] = @year_no";

            var parameters = new Dictionary<string, object>
                {
                    {"@year_no", year},
                    {"@month_no", month},
                    {"@updated_by", emp_seq}
                };
            ExecuteDataTable(strSQL, parameters);
        }

        #endregion
        
        #region Implement of IDAL_FIM_PayrollDeduction
        
        public IList<M_FIM_PayrollDeduction> GetPayrollDeductions(int? payroll_seq = null, 
            int? payroll_deduction_seq = null, string payroll_ref_no = null)
        {
            DataTable payrollDt = ExecuteDataTable("select payroll_seq from fim_payroll where ref_no = @payroll_ref_no and record_status != @delete_status", 
                new Dictionary<string, object>{{"@payroll_ref_no", payroll_ref_no}});
            var payroll_seq_from_ref_no = cn.ConvertNullInt(payrollDt.Rows.Count > 0 ? payrollDt.Rows[0][0] : null);

            List<M_FIM_PayrollDeduction> result = new List<M_FIM_PayrollDeduction>();
            string strSQL = @"SELECT [payroll_deduction_seq]      ,[payroll_seq]      ,     [title]
                                  ,[ConId]      ,[amount]      ,[record_status]       ,[created_by]      
                                  ,[created_when]      ,[updated_by]       ,[updated_when] 
                            FROM fim_payroll_deduction WHERE record_status != @delete_status " 
                + (payroll_seq.HasValue ? " AND payroll_seq = @payroll_seq " : string.Empty)
                + (payroll_deduction_seq.HasValue ? " AND payroll_deduction_seq = @payroll_deduction_seq " : string.Empty)
                + (!string.IsNullOrEmpty(payroll_ref_no) ? " AND payroll_seq = @payroll_seq_from_ref_no " : string.Empty);

            var parameters = new Dictionary<string, object>
                {
                    {"@payroll_seq", payroll_seq},
                    {"@payroll_deduction_seq", payroll_deduction_seq},
                    {"@payroll_seq_from_ref_no", payroll_seq_from_ref_no}
                };
            var datas = ExecuteDataTable(strSQL, parameters);

            var masContacts = GetIcmMasContact();
            foreach(DataRow data in datas.Rows)
            {
                var record = new M_FIM_PayrollDeduction();
                record.payroll_deduction_seq = Convert.ToInt32(data["payroll_deduction_seq"]);
                record.payroll_seq =  Convert.ToInt32(data["payroll_seq"]);
                record.title =  Convert.ToString(data["title"]);
                record.ConId =  Convert.ToInt32(data["ConId"]);
                record.amount =  Convert.ToDecimal(data["amount"]);
                record.record_status = Convert.ToString(data["record_status"]);
                record.created_by = Convert.ToInt32(data["created_by"]);
                record.created_when = Convert.ToDateTime(data["created_when"]);
                record.updated_by = cn.ConvertNullInt(data["updated_by"]);
                record.updated_when = cn.ConvertNullDateTime(data["updated_when"]);

                record.IcmMasContact = masContacts.FirstOrDefault(o => o.ConId == record.ConId);
                result.Add(record);
            }
            return result;
        }

        public void InsertPayrollDeduction(M_FIM_PayrollDeduction model)
        {
            string strSQL = @"INSERT INTO [dbo].[fim_payroll_deduction]
                                ([payroll_seq]
                                ,[title]
                                ,[ConId]
                                ,[amount]
                                ,[record_status]
                                ,[created_by]
                                ,[created_when])
                            VALUES
                                (@payroll_seq
                                ,@title
                                ,@ConId
                                ,@amount
                                ,@record_status
                                ,@created_by
                                ,GETDATE())";
            var parameters = new Dictionary<string, object>
                {
                    {"@payroll_seq", model.payroll_seq},
                    {"@title", model.title},
                    {"@ConId", model.ConId},
                    {"@amount", model.amount},
                    {"@record_status", model.record_status},
                    {"@created_by", model.created_by},
                };
            ExecuteDataTable(strSQL, parameters);
        }

        public void UpdatePayrollDeduction(M_FIM_PayrollDeduction model)
        {
            string strSQL = @"UPDATE [dbo].[fim_payroll_deduction]
                               SET [payroll_seq] = @payroll_seq
                                  ,[title] = @title
                                  ,[ConId] = @ConId
                                  ,[amount] = @amount
                                  ,[record_status] = @record_status
                                  ,[updated_by] = @updated_by
                                  ,[updated_when] = GETDATE()
                             WHERE payroll_deduction_seq = @payroll_deduction_seq";
            var parameters = new Dictionary<string, object>
                {
                    {"@payroll_deduction_seq", model.payroll_deduction_seq},
                    {"@payroll_seq", model.payroll_seq},
                    {"@title", model.title},
                    {"@ConId", model.ConId},
                    {"@amount", model.amount},
                    {"@record_status", model.record_status},
                    {"@updated_by", model.updated_by},
                };
            ExecuteDataTable(strSQL, parameters);
        }

        public void DeletePayrollDeduction(int payroll_deduction_seq, int emp_seq)
        {
            string strSQL = @"UPDATE [dbo].[fim_payroll_deduction]
                               SET [record_status] = @delete_status
                                  ,[updated_by] = @emp_seq
                                  ,[updated_when] = GETDATE()
                             WHERE payroll_deduction_seq = @payroll_deduction_seq";
            var parameters = new Dictionary<string, object>
                {
                    {"@payroll_deduction_seq", payroll_deduction_seq},
                    {"@emp_seq", emp_seq},
                };
            ExecuteDataTable(strSQL, parameters);
        }

        #endregion

        #region OldCode
        public DataTable ExecuteDataTable(string strSQL, Dictionary<string, object> parameters)
        {
            if(parameters == null) parameters = new Dictionary<string, object>();
            DataTable dt;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(parameters.Count + 2);
                int i = 0;
                dbManager.AddParameters(i++, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(i++, "@used_status", SystemInfo._ActiveRecordStatus);
                foreach (var kv in parameters)
                {
                    dbManager.AddParameters(i++, kv.Key, kv.Value);
                }

                var ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables.Count > 0 ? ds.Tables[0] : new DataTable();
            }
            catch (TimeoutException ex)
            {
                throw (ex);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return dt;
        }

        public DataTable PayrollTotal(int intMonth, int intYear)
        {
            DataTable dt = new DataTable();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@month", intMonth);
                dbManager.AddParameters(1, "@year", intYear);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = " select   a.payroll_seq, a.payroll_deduction_seq, a.DeductionTitle, a.ConId, a.NameTh As Name, "
                         + " a.DeducAmount As Total, a.bgm_source_seq, a.bgm_exp_grp_seq "
                         + " from (select p.payroll_seq, pd.payroll_deduction_seq, p.title as PayrollTitle, "
                         + " pd.title as DeductionTitle, pd.ConId, "
                         + " c.NameTh, pd.amount As DeducAmount , ms.bgm_source_seq, p.bgm_exp_grp_seq"
                         + " from fim_payroll p "
                         + " left join fim_payroll_deduction pd on p.payroll_seq = pd.payroll_seq "
                         +" LEFT JOIN bgm_mas_expense_group eg ON p.bgm_exp_grp_seq = eg.bgm_exp_grp_seq "
                         +" LEFT JOIN bgm_mas_source ms ON eg.bgm_source_seq = ms.bgm_source_seq "
                         + " inner join IcmMasContact c on pd.ConId = c.ConId "
                         + " where p.month_no = @month and p.year_no = @year and p.record_status != @record_status "
                         + " AND pd.record_status != @record_status AND pd.flag_sent_payment = 0) a "
                         + " group by a.DeductionTitle, a.ConId, a.NameTh, a.DeducAmount, a.payroll_deduction_seq, a.payroll_seq, a.bgm_source_seq, a.bgm_exp_grp_seq ";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                

            }
            catch (Exception ex)
            {
                
                throw(ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public DataTable PayrollCombine(int flag)
        {
            DataTable dt = new DataTable();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@payroll_sentpayment_combine", flag);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                //strSQL = " SELECT ps.payroll_sentpayment_name, c.ConId, c.NameTh, ps.amount "
                //        + " FROM fim_payroll_sentpayment ps "
                //        + " LEFT JOIN IcmMasContact c ON ps.ConId = c.ConId "
                //        + " WHERE ps.record_status <> @record_status AND  payroll_sentpayment_combine = @payroll_sentpayment_combine";

                strSQL = @"SELECT  (SELECT top 1( payroll_sentpayment_name) FROM fim_payroll_sentpayment
                            WHERE payroll_sentpayment_combine = @payroll_sentpayment_combine) AS name,
                            c.ConId, c.NameTh , SUM(pd.amount) as total
                            FROM fim_payroll_sentpayment ps 
                            LEFT JOIN IcmMasContact c ON ps.ConId = c.ConId 
                            LEFT JOIN fim_payroll_deduction pd ON ps.payroll_deduction_seq = pd.payroll_deduction_seq
                            WHERE ps.record_status <> @record_status AND  payroll_sentpayment_combine = @payroll_sentpayment_combine
                            GROUP BY c.ConId, c.NameTh";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];


            }
            catch (Exception ex)
            {

                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public string PaymentGroupName(int flag)
        {
            string strSQL;
            string PaymentName;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@flag", flag);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = " (SELECT TOP 1(payroll_sentpayment_name) FROM fim_payroll_sentpayment WHERE payroll_sentpayment_combine = @flag AND record_status <> @record_status) ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                PaymentName = Convert.ToString(dtb.Rows[0][0]);
                return PaymentName;
            }
            catch (Exception ex)
            {

                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public decimal SumAmount(int flag)
        {
            string strSQL;
            decimal SumAmount;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@flag", flag);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT sum(amount) as total FROM fim_payroll_sentpayment WHERE payroll_sentpayment_combine = @flag AND record_status <> @record_status GROUP BY ConId ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                SumAmount = Convert.ToDecimal(dtb.Rows[0][0]);
                return SumAmount;
            }
            catch (Exception ex)
            {

                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public string SumDeduction(int intMonth, int intYear)
        {
            string strSQL;
            string SumDeductions = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@month", intMonth);
                dbManager.AddParameters(1, "@year", intYear);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT SUM (a.DeducAmount) AS Total FROM ( "
		                  +" SELECT p.payroll_seq, p.title AS PayrollTitle, pd.title AS DeductionTitle,"
			              +" pd.ConId, c.NameTh, pd.amount AS DeducAmount FROM fim_payroll p "
		                  +" LEFT JOIN fim_payroll_deduction pd ON p.payroll_seq = pd.payroll_seq"
		                  +" INNER JOIN IcmMasContact c ON pd.ConId = c.ConId "
                          + " WHERE p.month_no = @month AND p.year_no = @year AND p.record_status != @record_status "
                          + " AND pd.record_status != @record_status) a ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];


                SumDeductions = (dtb.Rows[0][0]).ToString();
                return SumDeductions;

                
            }
            catch (Exception ex)
            {

                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertSentpayment(M_FIM_Payroll_Sentpayment info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@payroll_seq", info.payroll_seq);
                dbManager.AddParameters(1, "@payroll_deduction_seq", info.payroll_deduction_seq);
                dbManager.AddParameters(2, "@ConId", info.ConId);
                dbManager.AddParameters(3, "@payroll_sentpayment_name", info.payroll_sentpayment_name);
                dbManager.AddParameters(4, "@payroll_sentpayment_combine", info.payroll_sentpayment_combine);
                dbManager.AddParameters(5, "@amount", info.amount);
                dbManager.AddParameters(6, "@month", info.month_no);
                dbManager.AddParameters(7, "@year", info.year_no);
                dbManager.AddParameters(8, "@created_by", info.created_by);
                dbManager.AddParameters(9, "@status", SystemInfo._ActiveRecordStatus);

                strSQL = "INSERT INTO fim_payroll_sentpayment  "
                        +" (payroll_seq, payroll_deduction_seq, ConId, payroll_sentpayment_name, payroll_sentpayment_combine,"
                        + " amount, month_no, year_no, created_by,created_when, record_status) "
                        +" VALUES "
                        +" (@payroll_seq,"
                        +" @payroll_deduction_seq,"
                        +" @ConId,"
                        +" @payroll_sentpayment_name,"
                        +" @payroll_sentpayment_combine,"
                        +" @amount,"
                        +" @month,"
                        +" @year,"
                        +" @created_by,"
                        +" GETDATE(),"
                        +" @status)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertSentpaymentCombine(int ConId, string Name, decimal amount, int created_by, int intMonth
            , int intYear, int payrollSeq, int ChequeSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@ConId", ConId);
                dbManager.AddParameters(1, "@payroll_sentpayment_combine_name", Name);
                dbManager.AddParameters(2, "@amount", amount);
                dbManager.AddParameters(3, "@month", intMonth);
                dbManager.AddParameters(4, "@year", intYear);
                dbManager.AddParameters(5, "@payroll_seq", payrollSeq);
                dbManager.AddParameters(6, "@cheque_seq", ChequeSeq);
                dbManager.AddParameters(7, "@created_by", created_by);
                dbManager.AddParameters(8, "@status", SystemInfo._ActiveRecordStatus);

                strSQL = "INSERT INTO fim_payroll_sentpayment_combine  "
                        + " (ConId, payroll_sentpayment_combine_name,  "
                        + " amount, month_no, year_no, cheque_seq, created_by, created_when, record_status) "
                        + " VALUES "
                        + " (@ConId,"
                        + " @payroll_sentpayment_combine_name,"
                        + " @amount,"
                        + " @month,"
                        + " @year,"
                        + " @cheque_seq,"
                        + " @created_by,"
                        + " GETDATE(),"
                        + " @status)";


                dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void sentpaymentCombineSeq(int? intPayrollDeductionSeq, int intpayrollSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@payroll_deduction_seq", intPayrollDeductionSeq);
                dbManager.AddParameters(1, "@payroll_seq", intpayrollSeq);

                strSQL = " UPDATE fim_payroll_sentpayment SET "
                         + " payroll_sentpayment_combine_seq = (SELECT MAX(payroll_sentpayment_combine_seq) as lastSeq FROM fim_payroll_sentpayment_combine)"
                         + " WHERE payroll_deduction_seq = @payroll_deduction_seq AND payroll_seq = @payroll_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdatePayseqToCombine(int SentpaymentCombineSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@payroll_sentpayment_combine_seq", SentpaymentCombineSeq);

                strSQL = " UPDATE fim_payroll_sentpayment_combine SET "
                            +" pay_seq = (SELECT MAX(pay_seq) as lastSeq FROM fim_payments) "
                            + " WHERE payroll_sentpayment_combine_seq = @payroll_sentpayment_combine_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteSentpayment(int intPaySeq, int SentpaymentCombineSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@pay_seq", intPaySeq);
                dbManager.AddParameters(1, "@payroll_sentpayment_combine_seq", SentpaymentCombineSeq);

                strSQL = " DELETE fim_payroll_sentpayment "
                         + " WHERE payroll_sentpayment_combine_seq = @payroll_sentpayment_combine_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

             }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeletePayment(int intPaySeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@pay_seq", intPaySeq);

                strSQL = " DELETE fim_payments"
                         + " WHERE pay_seq = @pay_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);


            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteMoneyReq(int intPaySeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@pay_seq", intPaySeq);

                strSQL = " DELETE fim_money_requests "
                        + " WHERE money_req_seq = (SELECT money_req_seq FROM fim_payments "
                        + " WHERE pay_seq = @pay_seq)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteMoneyDtlReq(int intPaySeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@pay_seq", intPaySeq);

                strSQL = " DELETE fim_money_request_dtls "
                        +" WHERE money_req_dtls_seq =  (SELECT money_req_seq FROM fim_money_requests "
                        + " WHERE money_req_seq = (SELECT money_req_seq FROM fim_payments WHERE pay_seq = @pay_seq))";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteSentpaymentComb(int intMonth, int intYear)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@month", intMonth);
                dbManager.AddParameters(1, "@year", intYear);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " DELETE fim_payroll_sentpayment_combine "
                        + " where month_no = @month AND year_no = @year ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateFlagSentpayment(int? intPayrollDeductionSeq, int flagSentpayment)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@payroll_deduction_seq", intPayrollDeductionSeq);
                dbManager.AddParameters(1, "@flag_sent_payment", flagSentpayment);

                strSQL = " UPDATE fim_payroll_deduction SET "
                         +" flag_sent_payment = @flag_sent_payment"
                         +" WHERE payroll_deduction_seq = @payroll_deduction_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GridPayrollCombine(int intMonth, int intYear)
        {
            DataTable dt = new DataTable();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@month", intMonth);
                dbManager.AddParameters(1, "@year", intYear);
                dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT DISTINCT(pb.payroll_sentpayment_combine_seq), pb.payroll_sentpayment_combine_name"
                    + " , c.NameTh, c.ConId, pb.cheque_seq, pb.pay_seq,  pje.bgm_source_seq, ope.bgm_proj_oper_seq, p.bgm_proj_oper_exp_seq "
                    + ", CONCAT(CAST(cb.cheque_book_no as varchar), ' / ', CAST(che.cheque_no as varchar)) AS cheque_name"
                    + ", p.bgm_exp_grp_seq, sum(pd.amount) as amount"
                    + " FROM fim_payroll_sentpayment_combine pb "
                    + " LEFT JOIN IcmMasContact c on pb.ConId = c.ConId "
                    + " LEFT JOIN fim_payroll_sentpayment ps ON pb.payroll_sentpayment_combine_seq = ps.payroll_sentpayment_combine_seq "
                    + " LEFT JOIN fim_payroll_deduction pd ON ps.payroll_deduction_seq = pd.payroll_deduction_seq "
                    + " LEFT JOIN fim_cheques che ON pb.cheque_seq = che.cheque_seq"
                    + " LEFT JOIN fim_cheque_books cb ON che.cheque_book_seq = cb.cheque_book_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON che.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN fim_payroll p ON pd.payroll_seq = p.payroll_seq "
                    + " LEFT JOIN bgm_mas_expense_group eg ON p.bgm_exp_grp_seq = eg.bgm_exp_grp_seq "
                    + " LEFT JOIN bgm_project_operation_expense ope ON p.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq "
                    + " LEFT JOIN bgm_project_expense pje ON ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq "
                    + " WHERE pb.month_no = @month AND pb.year_no = @year AND pb.record_status <> @record_status"
                    + " group by pb.payroll_sentpayment_combine_seq, pb.payroll_sentpayment_combine_name" 
                        + ", c.NameTh, c.ConId, pb.cheque_seq, pb.pay_seq,  pje.bgm_source_seq, ope.bgm_proj_oper_seq, p.bgm_proj_oper_exp_seq"
                        + ", CONCAT(CAST(cb.cheque_book_no as varchar), ' / ', CAST(che.cheque_no as varchar)), p.bgm_exp_grp_seq";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {

                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dt;
        }

        public string RunningNumber()
        {
             string strSQL = "";
             string Number ;

             try
             {
                 dbManager.Open();
                 dbManager.CreateParameters(1);

                 dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                 strSQL = " SELECT max(payroll_sentpayment_combine) as RunningNumber FROM fim_payroll_sentpayment "
                         + " WHERE record_status != @status ";

                 DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

          
                Number = Convert.ToString(dtb.Rows[0][0]);
                return Number;
   
             }
             catch (Exception er)
             {
                 throw er;
             }
             finally
             {
                 dbManager.Dispose();
             }
         }

        public string RunningFlagGroup()
        {
            string strSQL = "";
            string Number;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT max(flag_group_payment) as RunningNumber FROM fim_payroll_deduction "
                        + " WHERE record_status != @status ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];


                Number = Convert.ToString(dtb.Rows[0][0]);
                return Number;

            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string BgmyearToPayment(int SentpaymentCombineSeq)
        {
            string strSQL = "";
            string BgmYear;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@payroll_sentpayment_combine_seq", SentpaymentCombineSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT DISTINCT(p.bgm_year_no) FROM fim_payroll_sentpayment_combine pc "
                        +" LEFT JOIN fim_payroll_sentpayment ps ON pc.payroll_sentpayment_combine_seq = ps.payroll_sentpayment_combine_seq "
                        +" LEFT JOIN fim_payroll_deduction pd ON ps.payroll_deduction_seq = pd.payroll_deduction_seq "
                        +" LEFT JOIN fim_payroll p ON pd.payroll_seq = p.payroll_seq "
                        + " WHERE ps.payroll_sentpayment_combine_seq = @payroll_sentpayment_combine_seq AND p.record_status <> @status ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];


                BgmYear = Convert.ToString(dtb.Rows[0][0]);
                return BgmYear;

            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertCheques(int paySeq, int? chequeSeq, decimal amount, int created_by)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@pay_seq", paySeq);
                dbManager.AddParameters(1, "@cheque_seq", chequeSeq);
                dbManager.AddParameters(2, "@amount", amount);
                dbManager.AddParameters(3, "@created_by", created_by);
                dbManager.AddParameters(4, "@status", SystemInfo._WorkRecordStatus);

                strSQL = "INSERT INTO fim_payment_cheques "
                        +" (pay_seq,"
                        +" cheque_seq,"
                        +" amount,"
                        +" created_by,"
                        +" created_when,"
                        +" record_status)"
                        +" VALUES "
                        + "((SELECT MAX(pay_seq) as lastSeq FROM fim_payments),"
                        +" @cheque_seq,"
                        +" @amount,"
                        +" @created_by,"
                        + " GETDATE(),"
                        + " @status)";


                dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        #endregion

        public IList<M_FIM_PayrollDtls> GetDetails(int year, int month)
        {
            string strSQL = "";
            IList<M_FIM_PayrollDtls> models = new List<M_FIM_PayrollDtls>();
            DataTable dtb;
            strSQL = @"select *
from fim_payroll_dtls
where [year] = @year and [month] = @month";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@month", month);
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    var model = new M_FIM_PayrollDtls();
                    model.payroll_dtls_seq = cn.ConvertNullInt(dtb.Rows[0]["payroll_dtls_seq"]) ?? 0;
                    model.year = cn.ConvertNullInt(dtb.Rows[0]["year"]) ?? 0;
                    model.month = cn.ConvertNullInt(dtb.Rows[0]["month"]) ?? 0;
                    model.emp_seq = model.year = cn.ConvertNullInt(dtb.Rows[0]["emp_seq"]) ?? 0; ;
                    model.salary = cn.ConvertNullDouble(dtb.Rows[0]["salary"]) ?? 0;
                    model.extra_add = cn.ConvertNullDouble(dtb.Rows[0]["extra_add"]) ?? 0;
                    model.previous = cn.ConvertNullDouble(dtb.Rows[0]["previous"]) ?? 0;
                    model.tax = cn.ConvertNullDouble(dtb.Rows[0]["tax"]) ?? 0;
                    model.coop = cn.ConvertNullDouble(dtb.Rows[0]["coop"]) ?? 0;
                    model.house_bank = cn.ConvertNullDouble(dtb.Rows[0]["house_bank"]) ?? 0;
                    model.office_loan = cn.ConvertNullDouble(dtb.Rows[0]["office_loan"]) ?? 0;
                    model.pf_staff = cn.ConvertNullDouble(dtb.Rows[0]["pf_staff"]) ?? 0;
                    model.other_deduction = cn.ConvertNullDouble(dtb.Rows[0]["other_deduction"]) ?? 0;
                    model.net = cn.ConvertNullDouble(dtb.Rows[0]["net"]) ?? 0;
                    model.pf_office = cn.ConvertNullDouble(dtb.Rows[0]["pf_office"]) ?? 0;
                    model.prof = cn.ConvertNullDouble(dtb.Rows[0]["prof"]) ?? 0;
                    model.survey_add = cn.ConvertNullDouble(dtb.Rows[0]["survey_add"]) ?? 0;
                    models.Add(model);
                }

                return models;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public M_FIM_PayrollDtls GetSumDetails(int year, int month)
        {
            string strSQL = "";
            var model = new M_FIM_PayrollDtls();
            DataTable dtb;
            strSQL = @"select year, month
, sum(coalesce(salary, 0)) as salary
, sum(coalesce(extra_add, 0)) as extra_add
, sum(coalesce(previous, 0)) as previous
, sum(coalesce(tax, 0)) as tax
, sum(coalesce(coop, 0)) as coop
, sum(coalesce(house_bank, 0)) as house_bank
, sum(coalesce(office_loan, 0)) as office_loan
, sum(coalesce(pf_staff, 0)) as pf_staff
, sum(coalesce(other_deduction, 0)) as other_deduction
, sum(coalesce(net, 0)) as net
, sum(coalesce(pf_office, 0)) as pf_office
, sum(coalesce(prof, 0)) as prof
, sum(coalesce(survey_add, 0)) as survey_add
from fim_payroll_dtls
where year = @year and month = @month
group by year, month
";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@month", month);
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    model.year = cn.ConvertNullInt(dtb.Rows[0]["year"]) ?? 0;
                    model.month = cn.ConvertNullInt(dtb.Rows[0]["month"]) ?? 0;
                    model.emp_seq = 1;
                    model.salary = cn.ConvertNullDouble(dtb.Rows[0]["salary"]) ?? 0;
                    model.extra_add = cn.ConvertNullDouble(dtb.Rows[0]["extra_add"]) ?? 0;
                    model.previous = cn.ConvertNullDouble(dtb.Rows[0]["previous"]) ?? 0;
                    model.tax = cn.ConvertNullDouble(dtb.Rows[0]["tax"]) ?? 0;
                    model.coop = cn.ConvertNullDouble(dtb.Rows[0]["coop"]) ?? 0;
                    model.house_bank = cn.ConvertNullDouble(dtb.Rows[0]["house_bank"]) ?? 0;
                    model.office_loan = cn.ConvertNullDouble(dtb.Rows[0]["office_loan"]) ?? 0;
                    model.pf_staff = cn.ConvertNullDouble(dtb.Rows[0]["pf_staff"]) ?? 0;
                    model.other_deduction = cn.ConvertNullDouble(dtb.Rows[0]["other_deduction"]) ?? 0;
                    model.net = cn.ConvertNullDouble(dtb.Rows[0]["net"]) ?? 0;
                    model.pf_office = cn.ConvertNullDouble(dtb.Rows[0]["pf_office"]) ?? 0;
                    model.prof = cn.ConvertNullDouble(dtb.Rows[0]["prof"]) ?? 0;
                    model.survey_add = cn.ConvertNullDouble(dtb.Rows[0]["survey_add"]) ?? 0;
                }

                return model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int InsertDetail(M_FIM_PayrollDtls model)
        {
            string strSQL = @"
INSERT INTO fim_payroll_dtls
(year, month, emp_seq
, salary, extra_add, previous, tax, coop, house_bank
, office_loan, pf_staff, other_deduction, net, pf_office, prof, survey_add)
VALUES (
@year, @month, @emp_seq
, @salary, @extra_add, @previous, @tax, @coop, @house_bank
, @office_loan, @pf_staff, @other_deduction, @net, @pf_office, @prof, @survey_add);

SELECT SCOPE_IDENTITY()";

            var parameters = new Dictionary<string, object>
                { 
                    {"@year", model.year},
                    {"@month", model.month},
                    {"@emp_seq", model.emp_seq},
                    {"@salary", model.salary},
                    {"@extra_add", model.extra_add},
                    {"@previous", model.previous},
                    {"@tax", model.tax},
                    {"@coop", model.coop},
                    {"@house_bank", model.house_bank},
                    {"@office_loan", model.office_loan},
                    {"@pf_staff", model.pf_staff},
                    {"@other_deduction", model.other_deduction},
                    {"@net", model.net},
                    {"@pf_office", model.pf_office},
                    {"@prof", model.prof},
                    {"@survey_add", model.survey_add},
                };

            try
            {
                DataTable dtLastId = ExecuteDataTable(strSQL, parameters);
                return Convert.ToInt32(dtLastId.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void InsertDetail(IList<M_FIM_PayrollDtls> models)
        {
            string strSQL = @"
INSERT INTO fim_payroll_dtls
(year, month, emp_seq
, salary, extra_add, previous, tax, coop, house_bank
, office_loan, pf_staff, other_deduction, net, pf_office, prof, survey_add)
VALUES (
@year, @month, @emp_seq
, @salary, @extra_add, @previous, @tax, @coop, @house_bank
, @office_loan, @pf_staff, @other_deduction, @net, @pf_office, @prof, @survey_add)";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);
                dbManager.BeginTransaction();

                foreach (var model in models)
                {
                    dbManager.AddParameters(0, "@payroll_dtls_seq", model.payroll_dtls_seq);
                    dbManager.AddParameters(1, "@year", model.year);
                    dbManager.AddParameters(2, "@month", model.month);
                    dbManager.AddParameters(3, "@emp_seq", model.emp_seq);
                    dbManager.AddParameters(4, "@salary", model.salary);
                    dbManager.AddParameters(5, "@extra_add", model.extra_add);
                    dbManager.AddParameters(6, "@previous", model.previous);
                    dbManager.AddParameters(7, "@tax", model.tax);
                    dbManager.AddParameters(8, "@coop", model.coop);
                    dbManager.AddParameters(9, "@house_bank", model.house_bank);
                    dbManager.AddParameters(10, "@office_loan", model.office_loan);
                    dbManager.AddParameters(11, "@pf_staff", model.pf_staff);
                    dbManager.AddParameters(12, "@other_deduction", model.other_deduction);
                    dbManager.AddParameters(13, "@net", model.net);
                    dbManager.AddParameters(14, "@pf_office", model.pf_office);
                    dbManager.AddParameters(15, "@prof", model.prof);
                    dbManager.AddParameters(16, "@survey_add", model.survey_add);

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }

                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAllDetail(int year, int month)
        {
            string strSQL = @"delete from fim_payroll_dtls where year = @year and month = @month";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@month", month);
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAllPayroll(int year, int month, int emp_seq)
        {
            string strSQL = @"
update fim_payroll
set record_status = @delete_status
, updated_by = @updated_by
, updated_when = @updated_when
where year_no = @year 
and month_no = @month";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@month", month);
                dbManager.AddParameters(2, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@updated_by", emp_seq);
                dbManager.AddParameters(4, "@updated_when", DateTime.Now);
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdatePayroll(int year, int month, int emp_seq)
        {
            try
            {
                int PayrollSeq;
                int PayrollSeq2;
                int PayrollSeq3;
                int PayrollSeq4;
                int PayrollSeq5;
                int PayrollSeq6;
                DeleteAllPayroll(year, month, emp_seq);
                var sumDetail = GetSumDetails(year, month);
                string MonthName = new DateTime(year, month, 1).ToString("MMMM", new System.Globalization.CultureInfo("th-TH"));

                var pr = new M_FIM_Payroll();
                pr.ref_no = new D_CodeGenerate().GetNextCode(TransectionType.FimPayrollRefNo, "");
                pr.title = "จ่ายเงินเดือนพนักงาน ประจำเดือน" + MonthName + " ปี " + year;
                pr.bgm_exp_grp_seq = 4;
                pr.amount = cn.ConvertNullDecimal(sumDetail.salary) ?? 0;
                pr.req_date = DateTime.Now;
                pr.emp_type_seq = 1;
                pr.record_status = "U";
                pr.ConId = 1;
                pr.month_no = month;
                pr.year_no = year;
                pr.bgm_year_no = DateTime.Now.Year + (DateTime.Now.Month >= 10 ? 544 : 543);
                pr.created_by = emp_seq;
                pr.created_when = DateTime.Now;
                PayrollSeq = InsertPayroll(pr);

                pr = new M_FIM_Payroll();
                pr.ref_no = new D_CodeGenerate().GetNextCode(TransectionType.FimPayrollRefNo, "");
                pr.title = "พชค.";
                pr.bgm_exp_grp_seq = 4;
                pr.amount = cn.ConvertNullDecimal(sumDetail.extra_add) ?? 0;
                pr.req_date = DateTime.Now;
                pr.emp_type_seq = 1;
                pr.record_status = "U";
                pr.ConId = 1;
                pr.month_no = month;
                pr.year_no = year;
                pr.bgm_year_no = DateTime.Now.Year + (DateTime.Now.Month >= 10 ? 544 : 543);
                pr.created_by = emp_seq;
                pr.created_when = DateTime.Now;
                PayrollSeq2 = InsertPayroll(pr);

                pr = new M_FIM_Payroll();
                pr.ref_no = new D_CodeGenerate().GetNextCode(TransectionType.FimPayrollRefNo, "");
                pr.title = "วิชาชีพ/ตำแหน่ง";
                pr.bgm_exp_grp_seq = 4;
                pr.amount = cn.ConvertNullDecimal(sumDetail.prof) ?? 0;
                pr.req_date = DateTime.Now;
                pr.emp_type_seq = 1;
                pr.record_status = "U";
                pr.ConId = 1;
                pr.month_no = month;
                pr.year_no = year;
                pr.bgm_year_no = DateTime.Now.Year + (DateTime.Now.Month >= 10 ? 544 : 543);
                pr.created_by = emp_seq;
                pr.created_when = DateTime.Now;
                PayrollSeq3 = InsertPayroll(pr);

                pr = new M_FIM_Payroll();
                pr.ref_no = new D_CodeGenerate().GetNextCode(TransectionType.FimPayrollRefNo, "");
                pr.title = "ตกเบิก";
                pr.bgm_exp_grp_seq = 4;
                pr.amount = cn.ConvertNullDecimal(sumDetail.previous) ?? 0;
                pr.req_date = DateTime.Now;
                pr.emp_type_seq = 1;
                pr.record_status = "U";
                pr.ConId = 1;
                pr.month_no = month;
                pr.year_no = year;
                pr.bgm_year_no = DateTime.Now.Year + (DateTime.Now.Month >= 10 ? 544 : 543);
                pr.created_by = emp_seq;
                pr.created_when = DateTime.Now;
                PayrollSeq4 = InsertPayroll(pr);

                pr = new M_FIM_Payroll();
                pr.ref_no = new D_CodeGenerate().GetNextCode(TransectionType.FimPayrollRefNo, "");
                pr.title = "จ่ายเงินสมทบกองทุนสำรองเลี้ยงชีพฯ ประจำเดือน" + MonthName + " ปี " + year;
                pr.bgm_exp_grp_seq = 4;
                pr.amount = cn.ConvertNullDecimal(sumDetail.pf_office) ?? 0;
                pr.req_date = DateTime.Now;
                pr.emp_type_seq = 1;
                pr.record_status = "U";
                pr.ConId = 1;
                pr.month_no = month;
                pr.year_no = year;
                pr.bgm_year_no = DateTime.Now.Year + (DateTime.Now.Month >= 10 ? 544 : 543);
                pr.created_by = emp_seq;
                pr.created_when = DateTime.Now;
                PayrollSeq5 = InsertPayroll(pr);

                pr = new M_FIM_Payroll();
                pr.ref_no = new D_CodeGenerate().GetNextCode(TransectionType.FimPayrollRefNo, "");
                pr.title = "ค่าตอบแทน Survey ตามผลการปฏิบัติงาน";
                pr.bgm_exp_grp_seq = 4;
                pr.amount = cn.ConvertNullDecimal(sumDetail.survey_add) ?? 0;
                pr.req_date = DateTime.Now;
                pr.emp_type_seq = 1;
                pr.record_status = "U";
                pr.ConId = 1;
                pr.month_no = month;
                pr.year_no = year;
                pr.bgm_year_no = DateTime.Now.Year + (DateTime.Now.Month >= 10 ? 544 : 543);
                pr.created_by = emp_seq;
                pr.created_when = DateTime.Now;
                PayrollSeq6 = InsertPayroll(pr);

                //============= Deduction ===============

                decimal dNetAmount = cn.ConvertNullDecimal((sumDetail.salary ?? 0) - (sumDetail.tax ?? 0)
                    - (sumDetail.coop ?? 0) - (sumDetail.house_bank ?? 0) - (sumDetail.office_loan ?? 0)
                    - (sumDetail.pf_staff ?? 0) - (sumDetail.other_deduction ?? 0)) ?? 0;
                var dd = new M_FIM_PayrollDeduction();
                dd.title = "เงินเดือน";
                dd.amount = dNetAmount;
                dd.payroll_seq = PayrollSeq;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "พชค.";
                dd.amount = cn.ConvertNullDecimal(sumDetail.extra_add) ?? 0;
                dd.payroll_seq = PayrollSeq2;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "วิชาชีพ/ตำแหน่ง";
                dd.amount = cn.ConvertNullDecimal(sumDetail.prof) ?? 0;
                dd.payroll_seq = PayrollSeq3;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "ตกเบิก";
                dd.amount = cn.ConvertNullDecimal(sumDetail.previous) ?? 0;
                dd.payroll_seq = PayrollSeq4;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "สมทบ PF";
                dd.amount = cn.ConvertNullDecimal(sumDetail.pf_office) ?? 0;
                dd.payroll_seq = PayrollSeq5;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "ค่าตอบแทน Survey ตามผลการปฏิบัติงาน";
                dd.amount = cn.ConvertNullDecimal(sumDetail.survey_add) ?? 0;
                dd.payroll_seq = PayrollSeq6;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "หักภาษี";
                dd.amount = cn.ConvertNullDecimal(sumDetail.tax) ?? 0; ;
                dd.payroll_seq = PayrollSeq;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "หักสหกรณ์";
                dd.amount = cn.ConvertNullDecimal(sumDetail.coop) ?? 0; ;
                dd.payroll_seq = PayrollSeq;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "หัก ธอส.";
                dd.amount = cn.ConvertNullDecimal(sumDetail.house_bank) ?? 0; ;
                dd.payroll_seq = PayrollSeq;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "หักกองทุนบ้าน สรพ.";
                dd.amount = cn.ConvertNullDecimal(sumDetail.office_loan) ?? 0; ;
                dd.payroll_seq = PayrollSeq;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "หักเงินสะสม PF";
                dd.amount = cn.ConvertNullDecimal(sumDetail.pf_staff) ?? 0; ;
                dd.payroll_seq = PayrollSeq;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);

                dd = new M_FIM_PayrollDeduction();
                dd.title = "หักอื่นๆ";
                dd.amount = cn.ConvertNullDecimal(sumDetail.other_deduction) ?? 0; ;
                dd.payroll_seq = PayrollSeq;
                dd.ConId = 1;
                dd.record_status = "U";
                dd.created_by = emp_seq;
                dd.created_when = DateTime.Now;
                InsertPayrollDeduction(dd);
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

//        public DataTable GetReport(int month, int year)
//        {
//            string strSQL = "";
//            DataTable dtb;
//            strSQL = @"select year, month
//, sum(coalesce(salary, 0)) as salary
//, sum(coalesce(extra_add, 0)) as extra_add
//, sum(coalesce(previous, 0)) as previous
//, sum(coalesce(tax, 0)) as tax
//, sum(coalesce(coop, 0)) as coop
//, sum(coalesce(house_bank, 0)) as house_bank
//, sum(coalesce(office_loan, 0)) as office_loan
//, sum(coalesce(pf_staff, 0)) as pf_staff
//, sum(coalesce(other_deduction, 0)) as other_deduction
//, sum(coalesce(net, 0)) as net
//, sum(coalesce(pf_office, 0)) as pf_office
//from fim_payroll_dtls
//where year = @year and month = @month
//group by year, month
//";

//            try
//            {
//                dbManager.Open();
//                dbManager.CreateParameters(2);
//                dbManager.AddParameters(0, "@year", year);
//                dbManager.AddParameters(1, "@month", month);
//                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

//                return dtb;
//            }
//            catch (Exception ex)
//            {
//                throw ex;
//            }
//            finally
//            {
//                dbManager.Dispose();
//            }
//        }
    }
}
